<?php

class Contacts extends Table
{
	static $titres = Array(0=>'Mr',1=>'Mme',2=>'Mlle');
	
	public static function LabelTitre($id)
	{
		switch ($id)
		{
		case 0:
			return 'Mme';
		case 1:
			return 'Mr';
		}
		return '';
	}

    // Ecrit le tableau des contacts d'un client
    public static function Tableau_cli($db, $cli_id)//-------------------------------------------------
    {
        $sql='SELECT con_id, con_nom, con_prenom, con_fonction, con_annee'.
            ' FROM contacts'.
            ' JOIN clients ON clients.cli_id = contacts.con_cli_id'.
            " WHERE con_cli_id =$cli_id";
        $champs = array ('con_nom', 'con_fonction', 'con_annee');
        $msg_vide = LNG_CON_VIDE;
		return parent::Tableau($db, $sql, $champs, $champs, $msg_vide, true);
    }//------------------------------------------------------------------Tableau

    // Ecrit le tableau des contacts d'un client
    public static function Tableau_rapide($db, $critere)//-------------------------------------------------
    {
        $sql='SELECT con_id, con_nom, con_prenom, con_fonction, con_annee'.
            ' FROM contacts'.
            ' JOIN clients ON clients.cli_id = contacts.con_cli_id'.
            " WHERE con_nom LIKE '%$critere%'".
            ' ORDER BY con_nom ASC';           
        $champs = array ('con_nom', 'con_fonction', 'con_annee');
        $msg_vide = LNG_CON_VIDE;
        return parent::Tableau($db, $sql, $champs, $champs, $msg_vide);
    }//-----------------------------------------------------------------

	public static function ConditionEmail(){//--------------------------
		return "(con_email IS NULL OR con_email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')";
    }//-----------------------------------------------------------------
	
	public static function ConditionAdresse(){//------------------------
		return "(cli_adresse = '' OR cli_codepostal = '' OR cli_ville = '')";
    }//-----------------------------------------------------------------

    public static function Tableau_mailing($db, $mai_id, $selection=-1, $adresse=-1)//-------------------------------------------------
    {
        $sql='SELECT env_mai_id as mai_id, env_envoi, cli_id, cli_nom, con_id, con_nom, con_prenom, con_fonction, con_annee'.
            ' FROM contacts'.
            ' JOIN clients ON clients.cli_id = contacts.con_cli_id'.
            ' JOIN envois ON envois.env_con_id = contacts.con_id';
        $where = array();
		if ($adresse==0)//mail
			$where[]= " (con_email IS NULL OR con_email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')";
		if ($adresse==1)//postal
			$where[]= " (cli_adresse = '' OR cli_codepostal = '' OR cli_ville = '') ";
		if ($selection!=-1)//contacts décochés ou cochés
			$where[]= "env_envoi=$selection";
		$where[]= " env_mai_id=$mai_id ";
		if (count($where)>0)			
			$sql .= " WHERE ".implode(' AND ', $where);
        $champs = array ('mai_liste', 'cli_nom', 'con_nom', 'con_fonction', 'con_annee');
        $champs_tri = array ('', 'cli_nom', 'con_nom', 'con_fonction', 'con_annee');
        $msg_vide = LNG_CON_VIDE;
		return parent::Tableau($db, $sql, $champs, $champs_tri, $msg_vide, true);
    }//------------------------------------------------------------------Tableau

	public static function Resume_Mailing($db, $mai_id){
		$envoi=" AND env_envoi=1";
		$sql = "SELECT count(distinct(con_cli_id)) AS nb 
				FROM envois 
				JOIN contacts ON contacts.con_id=envois.env_con_id 
				WHERE env_mai_id=$mai_id";
		if ($result=$this->db->query($sql.$envoi))
			if ($row=$result->fetch_assoc())
				$clients=$row['nb'];
		$sql = "SELECT count(env_con_id) AS nb 
				FROM envois 
				WHERE env_mai_id=$mai_id";
		if ($result=$this->db->query($sql.$envoi))
			if ($row=$result->fetch_assoc())
				$contacts=$row['nb'];
		$sql = "SELECT count(con_cli_id) AS nb 
				FROM envois 
				JOIN contacts ON contacts.con_id=envois.env_con_id 
				WHERE env_mai_id=$mai_id AND (con_email IS NULL OR con_email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')";
		if ($result=$this->db->query($sql.$envoi))
			if ($row=$result->fetch_assoc())
				$emails=$row['nb'];
		$sql = "SELECT count(env_con_id) AS nb 
				FROM envois 
				JOIN contacts ON contacts.con_id=envois.env_con_id 
				JOIN clients ON contacts.con_cli_id=clients.cli_id 
				WHERE env_mai_id=$mai_id AND (cli_adresse = '' OR cli_codepostal = '' OR cli_ville = '')";
		if ($result=$this->db->query($sql.$envoi))
			if ($row=$result->fetch_assoc())
				$adresses=$row['nb'];
		echo "$contacts contacts selectionnés ($clients Clients), dont $emails sans e-mail et $adresses sans adresse";		
	}

    public static function SelectTitre()
    {
		return Array( Array(0,'Mr'), Array(1,'Mme'), Array(2,'Mlle') );
	}
	
	public static function SelectContact($db, $cli_id, $con_id=-1){
        $sql="SELECT con_id, CONCAT_WS(', 'con_nom, con_prenom) as con_nom".
            ' FROM contacts'.
            ' JOIN clients ON clients.cli_id = contacts.con_cli_id'.
            " WHERE con_cli_id =$cli_id";
		return parent::Select($db, $sql, 'con_id', 'con_nom', $con_id);
	}
}


class Contact extends Enregistrement
{
	// Constructeur
	function __construct($db)//-------------------------------------------------
	{
		$this->defaut = Array(	'con_id' 		=> -1,
								'con_titre' 	=> -1,					
								'con_nom' 		=> '',
								'con_prenom' 	=> '',
								'con_fonction' 	=> '',
								'con_telephone' => '',
								'con_fax' 		=> '',
								'con_portable' 	=> '',
								'con_email' 	=> '',
								'con_annee' 	=> '',
								'con_cli_id' 	=> -1);						
		parent::__construct($db, 'contacts', 'con_id');
	}//--------------------------------------------------------------__construct

	// Verification des attributs via la base
	function Verification()//---------------------------------------------------
	{
		return True;
		echo print_r($this->erreurs);
	}//-------------------------------------------------------------Verification

	function Supprimer(){
		$sql = "DELETE FROM envois WHERE env_con_id={$this->id}";
		$this->db->query($sql);
		$sql = "DELETE FROM participants WHERE par_con_id={$this->id}";
		$this->db->query($sql);
		return parent::Supprimer();
	}

	function Ecrire(){
/*
		$annee=$this->attribut['con_annee'];
*/
		$this->attribut['con_annee'] = date('m/Y');
		$retour = parent::Ecrire();
		if ($retour){
			$sql = "UPDATE clients SET cli_annee='".date('Y-m-d')."' WHERE cli_id='{$this->attribut['con_cli_id']}'";
			$this->db->query($sql);
		}
		return $retour;
	}
	
	function NomFormat()
	{
		return parent::NomFormat('con_nom', 'con_prenom');
	}
	
	function Traitement()
	{
		if ( isset( $_POST['maj_sup'] ) )
		{
			if ( isset( $_POST['confirmation'] ) )
				if ( $this->Supprimer() )
				{
					$_SESSION[SES_STATUT] = LNG_STA_UTI_SUP_0;
					header('location:clicontacts.php?id='.+$this->attribut['con_cli_id']);
				}
				else
					$_SESSION[SES_STATUT] = LNG_STA_ERREUR.LNG_STA_UTI_SUP_1;
			else
				$erreur['confirmation'] = LNG_UTI_CONFIRMATION_AIDE;
			return;
		}
		if (parent::Traitement()){
			if ( isset($_GET['part_key']) ){
			
				header('location:rappparticipant.php?id='.$_GET['rap_id'].'&part_key='.$_GET['part_key'].'&part_num='.$_GET['part_num'].'&par_con_id='.$this->attribut['con_id'].'&par_cli_id='.$this->attribut['con_cli_id']);
			}
			else
				header('location:clicontacts.php?id='.$this->attribut['con_cli_id']);
		}
	}
	function Contenu($erreur,$onglet=0, $edition=False)//-----------------------
	{
		$h4 = LNG_H4_INFORMATIONS;
		// Affichage du contenu
		if ( droit_modifier(GR_MAC_MODIF, $edition) )
			$h4 .= Liens::ConModifier($this->cle());
		$participant = '';
		if ( isset($_GET['part_key']) )
			$participant = "&amp;rap_id={$_GET['rap_id']}&amp;part_key={$_GET['part_key']}&amp;part_num={$_GET['part_num']}";
		?>
		<form method='post' action='contact.php?<?="id={$this->cle()}&amp;edition=$edition$participant"?>'>
		<?php
		dohtml_h4($h4);
		$this->ChampSelect('con_cli_id', $edition);
		$this->ChampText('con_nom', $edition);
		$this->ChampText('con_prenom', $edition);
		$this->ChampText('con_fonction', $edition);
		$this->ChampText('con_telephone', $edition);
		$this->ChampText('con_fax', $edition);
		$this->ChampText('con_portable', $edition);
		$this->ChampText('con_email', $edition);
		$this->ChampText('con_annee', False);
		if ($edition)
			dohtml_formbouton('labelagauche', 'Mettre à jour la base', 'maj_infos', 'Annuler les modifications');
		?>
		</form>
		<?php
		if ($this->cle() != -1 && $_SESSION[SES_USER_NIVEAU]>=GR_CON_SUPPR){
		?>
			<form method='post' action='contact.php?<?="id={$this->cle()}"?>'>
			<?php	
					dohtml_h4(LNG_H4_SUPPRESSION);
					dohtml_formcheck('labelagauche',
									'confirmation',
									LNG_UTI_CONFIRMATION,
									LNG_UTI_CONFIRMATION_AIDE,
									$erreur['confirmation']);
					dohtml_formbouton('labelagauche', 'Supprimer', 'maj_sup');
			?>
			</form>
		<?php
		}
	}
}
